package com.zl_huang.example.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.IntegerType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.zl_huang.example.model.QueryCondition;
import com.zl_huang.example.model.Score;
@Repository
public class ScoreDaoHibernateImpl extends BaseHibernateDAO<Score> implements IScoreDaoHibernate {
	@Autowired
	private SessionFactory sessionFactory;
	@Transactional
	public Session getSession(){
		return this.sessionFactory.getCurrentSession();
	}

	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Score getScoreByUserIdAndSubId(String user_id, Integer sub_id) throws Exception {
		Session session = getSession();
		String hql = "from Score score where score.user_id = "+user_id+" and score.subject_id = "+sub_id;
		List<Score> subjectList = session.createQuery(hql).list();
		if(!subjectList.isEmpty()){
			return subjectList.get(0);
		}else{
			return null;
		}
		
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Score getScoreById(Integer scoreId) throws Exception {
		Session session = getSession();
		String hql = "from Score score where score.id = "+ scoreId;	
		List<Score> scoreList = session.createQuery(hql).list();
		if(!scoreList.isEmpty()){
			return scoreList.get(0);
		}else{
			return null;
		}
		
	}


	@Override
	@Transactional
	public boolean editScore(Score score) throws Exception {
		Session session = getSession();
		session.update(score);
		return true;
	}


	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Score> getAllScore() throws Exception {
		Session session = getSession();
		String hql = "from Score";
		List<Score> scoreList = session.createQuery(hql).list();
		if(!scoreList.isEmpty()){
			return scoreList;
		}else{
			return null;
		}
	}


	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Score> getSelectedScore(QueryCondition queryCondition) throws Exception {
		/*select es.id as id from exam_score es
		right join exam_user as eu on es.user_id = eu.user_id and eu.user_id = "201704" and eu.user_name="Stevern"
		right join exam_subject as esub on esub.sub_id = es.subject_id and esub.name = "数学"
		where DATE_FORMAT(es.exam_time,'%Y-%m-%d') = "2017-05-05"
		*/
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select es.id as id from (exam_score es)");		
		sql.append("  right join exam_user as eu on es.user_id = eu.user_id  ");
		if(!"NONE".equals(queryCondition.getUser_id().trim())){
			sql.append(" and eu.user_id = '" +queryCondition.getUser_id()+"'");
		}
		if(!"NONE".equals(queryCondition.getUser_name().trim())){
			sql.append(" and eu.user_name = '" + queryCondition.getUser_name().trim()+"'");
		}
		sql.append(" right join exam_subject as esub on esub.sub_id = es.subject_id ");
		if(!"NONE".equals(queryCondition.getName().trim() )){
			sql.append("  and esub.name = '" + queryCondition.getName()+"'");
		}
		if(!queryCondition.getExam_time().equals("")){
			sql.append("   where DATE_FORMAT(es.exam_time,'%Y-%m-%d') = '" + queryCondition.getExam_time()+"'");
		}else{
			sql.append("   where 1=1");
		}
		System.out.println(sql);
		List<Integer> idList =  (List<Integer>)this.createSqlQuery(String.valueOf(sql))
				.addScalar("id", IntegerType.INSTANCE).list();
		List<Score> scoreList = new ArrayList<Score>();
		for(Integer id : idList){
			if(id != null){
			    Score score = getScoreById(id);
			    System.out.println("id是"+score.getId());
			    if(score != null){
			    	scoreList.add(score);
			    }
			}
		}
		return scoreList;
		
	}

}
